package scfg.output.database;

import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;

import desposito6.math.iDouble;
import scfg.AlignedSequence;
import scfg.KHGrammar;
import scfg.RnaAlgoPrediction;

public class GrammarSqlGenerator {
	
	private static String appendString(String val) {
		if(val == null)
			return "NULL";
		return "'" + val + "'";
	}
	
	private static String appendString(Integer val) {
		if(val == null)
			return "NULL";
		return val.toString();
	}
	
	public static String[] KnudsenHeinSqlInsert(KHGrammar grammar) {
		String[] arr = new String[4];
		StringBuilder sb = new StringBuilder("INSERT INTO `GrammarDB`.`structure_probabilities` ");
		sb.append("(`strid`, `S_L`, `S_LS`, `L_s`, `L_dFd`, `F_LS`, `F_dFd`) VALUES (NULL, ");
		iDouble[] temp = grammar.get_kh_params();
		sb.append(temp[0]).append(", ");
		sb.append(temp[0].sub(1).abs()).append(", ");
		sb.append(temp[1]).append(", ");
		sb.append(temp[1].sub(1).abs()).append(", ");
		sb.append(temp[2]).append(", ");
		sb.append(temp[2].sub(1).abs()).append(");");
		arr[0] = sb.toString();
		
		sb = new StringBuilder("INSERT INTO `GrammarDB`.`altered_structure_probabilities` ");
		sb.append("(`astrid`, `L_T`, `L_D`, `F_C`, `F_E`) VALUES (NULL, ");
		temp = grammar.get_kh_alt_params();
		sb.append(temp[0]).append(", ");
		sb.append(temp[1]).append(", ");
		sb.append(temp[2]).append(", ");
		sb.append(temp[3]).append(");");
		arr[1] = sb.toString();
		
		sb = new StringBuilder("INSERT INTO  `GrammarDB`.`state_percentages` ");
		sb.append("(`stpid`, `S_st`, `L_st`, `F_st`, `T_st`, `D_st`, `C_st`, `E_st`) VALUES (NULL, ");
		temp = grammar.get_kh_perc();
		sb.append(temp[0]).append(", ");
		sb.append(temp[1]).append(", ");
		sb.append(temp[2]).append(", ");
		// TODO: Actual Values
//		sb.append(temp[3]).append(", ");
//		sb.append(temp[4]).append(", ");
//		sb.append(temp[5]).append(", ");
//		sb.append(temp[6]).append(");");
		sb.append("-1").append(", ");
		sb.append("-1").append(", ");
		sb.append("-1").append(", ");
		sb.append("-1").append(");");
		arr[2] = sb.toString();
		
		sb = new StringBuilder("INSERT INTO `GrammarDB`.`knudsen_hein_training_set` ");
		sb.append("(`khid`, `strid`, `astrid`, `stpid`, `name`, `notes`) VALUES ");
		sb.append("(NULL, %strid%, %astrid%, %stpid%, '");
		sb.append(grammar.getName()).append("', ");
		sb.append("NULL").append(");");
		arr[3] = sb.toString();
		
		return arr;
	}
	
	public static String[] AlignedSequenceSqlInsert(AlignedSequence alnseq) {
		String[] arr = new String[4];
		StringBuilder sb = new StringBuilder("INSERT INTO `RnaSeqDB`.`seqinfo` ");
		sb.append("(`name`, `class`, `subclass`) VALUES (");
//		sb.append(appendString(alnseq.getAccessionNumber())).append(", ");
		sb.append(appendString(alnseq.getName())).append(", ");
		sb.append(appendString(alnseq.getRnaClass())).append(", ");
		sb.append(appendString(alnseq.getRnaSubClass())).append(");");
		arr[0] = sb.toString();
		
		sb = new StringBuilder("INSERT INTO `RnaSeqDB`.`sequences` ");
		sb.append("(`alignment`, `sequence`, `seqlength`) VALUES (");
		sb.append(appendString(alnseq.getAlignment())).append(", ");
		sb.append(appendString(alnseq.getSequence())).append(", ");
		sb.append(alnseq.getSequenceLength()).append(");");
		arr[1] = sb.toString();

		sb = new StringBuilder("INSERT INTO `RnaSeqDB`.`filtervals` ");
		sb.append("(`ambiguous`, `duplicate`, `inalignment`, `dense`, `densecomp`) VALUES (");
		sb.append(alnseq.getAmbiguousString()).append(", ");
		sb.append(alnseq.getDuplicateString()).append(", ");
		sb.append(alnseq.getInAlignmentString()).append(", ");
		sb.append(alnseq.getDenseString()).append(", ");
		sb.append(alnseq.getDenseCompString()).append(");");
		arr[2] = sb.toString();
		
		sb = new StringBuilder("INSERT INTO `RnaSeqDB`.`alnseqs` ");
		sb.append("(`seqid`, `filid`, `infoid`) VALUES ");
		sb.append("(%seqid%, %filid%, %infoid%);");
		arr[3] = sb.toString();
		
		return arr;
	}
	
	public static String[] AlignedSequenceSqlUpdate(AlignedSequence alnseq) {
		String[] arr = new String[4];
		StringBuilder sb = new StringBuilder("UPDATE `RnaSeqDB`.`seqinfo` SET ");
		sb.append("`name`=").append(appendString(alnseq.getName())).append(", ");
		sb.append("`class`=").append(appendString(alnseq.getRnaClass())).append(", ");
		sb.append("`subclass`=").append(appendString(alnseq.getRnaSubClass()));
		sb.append(" WHERE `seqinfo`.`infoid`=").append(alnseq.getInfoid()).append(";");
		arr[0] = sb.toString();
		
		sb = new StringBuilder("UPDATE `RnaSeqDB`.`sequences` SET ");
		sb.append("`alignment`=").append(appendString(alnseq.getAlignment())).append(", ");
		sb.append("`sequence`=").append(appendString(alnseq.getSequence())).append(", ");
		sb.append("`seqlength`=").append(alnseq.getSequenceLength());
		sb.append(" WHERE `sequences`.`seqid`=").append(alnseq.getSeqid()).append(";");
		arr[1] = sb.toString();

		sb = new StringBuilder("UPDATE `RnaSeqDB`.`filtervals` SET ");
		sb.append("`ambiguous`=").append(alnseq.getAmbiguousString()).append(", ");
		sb.append("`duplicate`=").append(alnseq.getDuplicateString()).append(", ");
		sb.append("`inalignment`=").append(alnseq.getInAlignmentString()).append(", ");
		sb.append("`dense`=").append(alnseq.getDenseString()).append(", ");
		sb.append("`densecomp`=").append(alnseq.getDenseCompString());
		sb.append(" WHERE `filtervals`.`filid`=").append(alnseq.getFilid()).append(";");
		arr[2] = sb.toString();
		
		sb = new StringBuilder("UPDATE `RnaSeqDB`.`alnseqs` SET ");
		sb.append("`seqid`=").append(alnseq.getSeqid()).append(", ");
		sb.append("`filid`=").append(alnseq.getFilid()).append(", ");
		sb.append("`infoid`=").append(alnseq.getInfoid());
		sb.append(" WHERE `alnseqs`.`alnseqid`=").append(alnseq.getAlnseqid()).append(";");
		arr[3] = sb.toString();
		
		return arr;
	}
	
	public static HashMap<String, RnaAlgoPrediction> PredictionSqlUpdate(AlignedSequence alnseq) {
		HashMap<String, RnaAlgoPrediction> predictions = new HashMap<String, RnaAlgoPrediction>();
		
		for (RnaAlgoPrediction pred : alnseq.getAllStructures()) {
			if (pred.getAlgo() == null || pred.getStructure() == null) {
				// Ignore this prediction
			}
			else if (pred.getPredid() == null) {
				StringBuilder sb = new StringBuilder("INSERT `RnaSeqDB`.`structures` ");
				sb.append("(`alnseqid`, `algo`, `structure`) VALUES (");
				sb.append(alnseq.getAlnseqid()).append(", ");
				sb.append(appendString(pred.getAlgo())).append(", ");
				sb.append(appendString(pred.getStructure())).append(");");
				predictions.put(sb.toString(), pred);
			} else {
				StringBuilder sb = new StringBuilder("UPDATE `RnaSeqDB`.`structures` SET ");
				sb.append("`alnseqid`=").append(alnseq.getAlnseqid()).append(", ");
				sb.append("`algo`=").append(appendString(pred.getAlgo())).append(", ");
				sb.append("`structure`=").append(appendString(pred.getStructure()));
				sb.append(" WHERE `structures`.`predid`=").append(pred.getPredid()).append(";");
				predictions.put(sb.toString(), null);
			}
		}
		
		for (RnaAlgoPrediction pred : alnseq.getDeletedStructures()) {
			if (pred.getPredid() != null) {
				StringBuilder sb = new StringBuilder("DELETE FROM `RnaSeqDB`.`structures` WHERE `predid`=");
				sb.append(alnseq.getAlnseqid()).append(";");
				predictions.put(sb.toString(), null);
			}
		}
		
		return predictions;
	}

	public static HashMap<String, DBAccessionNumberPair> AccessionNumberSqlUpdate(
			AlignedSequence alnseq) {
		// TODO Auto-generated method stub
		HashMap<String, DBAccessionNumberPair> predictions = new HashMap<String, DBAccessionNumberPair>();
		
		for (DBAccessionNumberPair pred : alnseq.getAccessionNumber()) {
			if (pred.getAccessionNumber() == null) {
				// Ignore this Accession Number
			}
			else if (pred.getId() == null) {
				StringBuilder sb = new StringBuilder("INSERT `RnaSeqDB`.`accessionnumbers` ");
				sb.append("(`alnseqid`, `accessionnumber`) VALUES (");
				sb.append(appendString(alnseq.getAlnseqid())).append(", ");
				sb.append(appendString(pred.getAccessionNumber())).append(");");
				predictions.put(sb.toString(), pred);
			} else {
				StringBuilder sb = new StringBuilder("UPDATE `RnaSeqDB`.`accessionnumbers` SET ");
				sb.append("`alnseqid`=").append(alnseq.getAlnseqid()).append(", ");
				sb.append("`accessionnumber`=").append(appendString(pred.getAccessionNumber()));
				sb.append(" WHERE `accessionnumbers`.`accid`=").append(pred.getId()).append(";");
				predictions.put(sb.toString(), null);
			}
		}
		return predictions;
	}
}
